﻿using System;
using System.Collections;
using System.Data;
using System.IO;
using System.Text.RegularExpressions;

namespace Tab2Sql
{
    internal class CSVtoDataTable
    {
        /// <summary>
        /// Most of this is probably redundant. (April 2010)
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public DataTable ParseCSVFile(string path)
        {
            string inputString = "";

            // check that the file exists before opening it
            if (File.Exists(path))
            {
                var sr = new StreamReader(path);
                inputString = sr.ReadToEnd();
                sr.Close();
            }

            return ParseCSV(inputString);
        }

        /// <summary>
        /// Most of this is probably redundant (April 2010).
        /// </summary>
        /// <param name="inputString"></param>
        /// <returns></returns>
        public DataTable ParseCSV(string inputString)
        {
            var dt = new DataTable();

            // declare the Regular Expression that will match versus the input string
            var re = new Regex("((?<field>[^\",\\r\\n]+)|\"(?<field>([^\"]|\"\")+)\")(,|(?<rowbreak>\\r\\n|\\n|$))");

            var colArray = new ArrayList();
            var rowArray = new ArrayList();
            var colHeaders = new ArrayList();
            int colCount = 0;
            int maxColCount = 0;
            string rowbreak = "";
            string field = "";

            MatchCollection mc = re.Matches(inputString);
            int rowNumber = 0;
            foreach (Match m in mc)
            {
                // retrieve the field and replace two double-quotes with a single double-quote
                field = m.Result("${field}").Replace("\"\"", "\"");

                rowbreak = m.Result("${rowbreak}");

                if (field.Length > 0)
                {
                    if (rowNumber == 0)
                    {
                        colHeaders.Add(field);
                    }
                    colArray.Add(field);
                    colCount++;
                }

                if (rowbreak.Length > 0)
                {
                    rowArray.Add(colArray.ToArray());
                    colArray = new ArrayList();
                    if (colCount > maxColCount)
                    {
                        maxColCount = colCount;
                    }
                    colCount = 0;
                    rowNumber++;
                }
            }

            if (rowbreak.Length == 0)
            {
                // this is executed when the last line doesn't
                // end with a line break
                rowArray.Add(colArray.ToArray());
                if (colCount > maxColCount)
                    maxColCount = colCount;
            }

            // create the columns for the table
            for (int i = 0; i < maxColCount; i++)
            {
                try
                {
                    dt.Columns.Add(colHeaders[i].ToString());
                }
                catch
                {
                    dt.Columns.Add(String.Format("col{0:000}", i));
                }
            }
            // dt.Columns.Add(String.Format("col{0:000}", i));


            // convert the row Array List into an Array object for easier access
            Array ra = rowArray.ToArray();
            for (int i = 0; i < ra.Length; i++)
            {
                // create a new DataRow
                DataRow dr = dt.NewRow();

                // convert the columnName Array List into an Array object for easier access
                var ca = (Array) (ra.GetValue(i));

                // add each field into the new DataRow
                for (int j = 0; j < ca.Length; j++)
                    dr[j] = ca.GetValue(j);

                // add the new DataRow to the DataTable
                dt.Rows.Add(dr);
            }

            // in case no data was parsed, create a single columnName
            if (dt.Columns.Count == 0)
                dt.Columns.Add("NoData");

            return dt;
        }
    }
}